import logging
import pymysql
import datetime
import traceback
from core.config import Config
from core.my_redis import MyRedis


class Result(object):
    def __init__(self):
        self.shop_id
        self.order_num_1 = 0
        self.order_num_2 = 0
        self.rider_num_1 = 0
        self.rider_num_2 = 0
        self.rider_num_3 = 0
        self.rider_num_4 = 0
        self.pressure

    @classmethod
    def writer_result_data(cls, shop_id, order_num_obj, rider_num_obj):

        rider_count = rider_num_obj.rider_num_1 + rider_num_obj.rider_num_2 + rider_num_obj.rider_num_3 + rider_num_obj.rider_num_4
        if rider_count == 0:
            logging.info("骑手数量为0，不统计")
            return

        order_num = order_num_obj.order_num_1*0.6 + order_num_obj.order_num_2*0.4

        tmp_result_list = list()
        tmp_result_list.append([
            shop_id,
            order_num_obj.order_num_1,
            order_num_obj.order_num_2,
            rider_num_obj.rider_num_1,
            rider_num_obj.rider_num_2,
            rider_num_obj.rider_num_3,
            rider_num_obj.rider_num_4,
            order_num / rider_count
        ])

        # 连接数据库
        db = pymysql.connect(**Config.db_bi)

        try:
            cursor = db.cursor()
            sql = """INSERT INTO db_bi.shop_delivery_pressure_stat(
                    shop_id,
                    order_num_1,
                    order_num_2,
                    rider_num_1,
                    rider_num_2,
                    rider_num_3,
                    rider_num_4,
                    pressure
                )VALUES (
                    %s, %s, %s, %s, %s, 
                    %s, %s, %s
                )"""
            cursor.executemany(sql, (tmp_result_list))

            db.commit()
            logging.info(f"统计结果插入数据库成功，{shop_id}")
        except Exception as e:
            traceback.print_exc()
            logging.info(f"统计结果插入数据库失败，{shop_id},异常原因：{e}")
        finally:
            db.close()


    @classmethod
    def delete_one_month_ago_data(cls, shop_id):

        start = datetime.datetime.now()
        end = start - datetime.timedelta(days=Config.data_retention_time_day)
        line = -1
        # 连接数据库
        db = pymysql.connect(**Config.db_bi)
        try:
            cursor = db.cursor()
            sql = """DELETE 
                       FROM
                         db_bi.shop_delivery_pressure_stat
                       WHERE shop_id = %s 
                         AND created_time <= %s """
            line = cursor.execute(sql, (shop_id, end))
            db.commit()
            logging.info(f"删除{end}之前统计数据 {shop_id}, 删除{line}行")

        except Exception as e:
            logging.error(f"删除{end}之前统计数据 {shop_id}, 失败：{e}")
            db.rollback()
        finally:
            db.close()

        return line

    @classmethod
    def flush_redis(self, shop_id,config_server_obj):

        # 获取当前压力阈值
        threshold = self.get_shop_pressure_threshold(shop_id,config_server_obj)
        if threshold is None:
            return

        # # 获取当前压力值
        # curr_pressure = self.get_shop_pressure(shop_id)
        # if curr_pressure is None:
        #     return

        # 存入redis   or:doaj:curr:pressure:9M0K    = {"curr":123,"threshold":54,"percent":60}
        key = f"or:doaj:curr:pressure:{shop_id}"
        #val = {"curr":curr_pressure, "threshold":threshold,"percent":config_server_obj.pressure_percent}
        val = { "threshold":threshold}

        logging.info(f"key:  {key}    val:  {str(val)}")
        MyRedis.setAndExp(key,str(val),5*60)

    @classmethod
    def get_shop_pressure(self, shop_id):
        db = pymysql.connect(**Config.db_bi)
        try:
            cursor = db.cursor()
            sql = """
                SELECT
                    id,
                    pressure
                FROM db_bi.shop_delivery_pressure_stat
                WHERE 
                shop_id = %s
                order by id desc
                limit 1
            """
            cursor.execute(sql, (shop_id))
            results = cursor.fetchall()

            data_id = None
            curr_pressure = None
            for row in results:
                data_id = int(row["id"])
                curr_pressure = float(row["pressure"])
                break

            logging.info(f"门店{shop_id}当前压力值:{curr_pressure},表id:{data_id}")
            return curr_pressure

        except Exception as e:
            traceback.print_exc()
            logging.error(f"获取当前压力值失败: {e}")
        finally:
            db.close()

    @classmethod
    def get_shop_pressure_threshold(self, shop_id, config_server_obj):

        now = datetime.datetime.now()
        start = now - datetime.timedelta(days=Config.refer_data_range_day)

        db = pymysql.connect(**Config.db_bi)
        try:
            cursor = db.cursor()
            sql = """
                SELECT
                    count(*) as data_cnt
                FROM db_bi.shop_delivery_pressure_stat
                WHERE 
                shop_id = %s
                AND created_time >= %s
            """
            cursor.execute(sql, (shop_id, start))
            results = cursor.fetchall()

            data_cnt = 0

            # 解析数据
            for row in results:
                data_cnt = int(row["data_cnt"])

            if data_cnt is None or data_cnt< Config.data_min_size:
                logging.info(f"门店{shop_id}积累的数据量少于{Config.data_min_size}，不能做为阈值")
                return None

            sql = """
                            SELECT
                                id,
                                pressure
                            FROM db_bi.shop_delivery_pressure_stat
                            WHERE 
                            shop_id = %s
                            AND created_time >= %s
                            order by pressure ASC
                        """
            cursor.execute(sql, (shop_id, start))
            results = cursor.fetchall()

            pressure_id = data_cnt * config_server_obj.pressure_percent // 100

            row_num = 0
            threshold = None
            data_id = None
            for row in results:
                row_num += 1
                if row_num == pressure_id:
                    data_id = int(row["id"])
                    threshold = float(row["pressure"])
                    break

            logging.info(f"门店{shop_id}使用的压力阈值:{threshold},表id:{data_id}, 分位数:{config_server_obj.pressure_percent}")
            return threshold

        except Exception as e:
            traceback.print_exc()
            logging.error(f"获取表中压力阈值失败: {e}")
        finally:
            db.close()
